﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIP;
using yunbo.Model.PPMS;

namespace yunbo.Repositorys.PPMSRepositorys
{
    public class Mes_SCDDRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public List<Mes_SCDD> GetMes_SCDD()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "select* from mes_scdd";
                List<Mes_SCDD> mes_SCDD = connection.Query<Mes_SCDD>(sql).ToList(); 
                return mes_SCDD;
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="scdd"></param>
        /// <returns></returns>
        public int AddMes_SCDD(ADDSCDD scdd)
        {
            string sql = @"INSERT INTO mes_scdd (scdd_zt, scdd_yxj, scdd_jd, scdd_code, scdd_lx, scdd_xz, ks_code,ks_name, ht_code, seddbz, scdd_sl, scdd_wcsl, delivery_date, planned_start_time, planned_completion_time, create_by, create_time)
            VALUES (44, @scdd_yxj, 0, @scdd_code, @scdd_lx, @scdd_xz, @ks_code,@ks_name, @ht_code, @seddbz, @scdd_sl, 0, @delivery_date, @planned_start_time, @planned_completion_time, @create_by, GETDATE())";
            try
            {
                    using (SqlConnection connection = new SqlConnection(data))
                    {
                        connection.Open();
                        var parameters = new
                        {
                            scdd_yxj = scdd.scdd_yxj,
                            scdd_code = scdd.scdd_code,
                            scdd_lx = scdd.scdd_lx,
                            scdd_xz = scdd.scdd_xz,
                            ks_code = scdd.ks_code,
                            ks_name=scdd.ks_name,
                            ht_code = scdd.ht_code,
                            seddbz = scdd.seddbz,
                            scdd_sl = scdd.scdd_sl,
                            delivery_date = scdd.delivery_date,
                            planned_start_time = scdd.planned_start_time,
                            planned_completion_time = scdd.planned_completion_time,
                            create_by = scdd.create_by,
                        };

                        var affectedRows = connection.Execute(sql, parameters);
                        return affectedRows;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    throw;
                }
            }

        public int UpdateStatus(string code, int state, int update_by)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "UPDATE mes_scdd SET scdd_zt = @state, update_by = @update_by, update_time = GETDATE() WHERE scdd_code = @code";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@code", code);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@state", state); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        public int UpdateMes_SCDD(ADDSCDD scdd)
        {
            string sql = @"UPDATE mes_scdd SET scdd_yxj = @scdd_yxj,scdd_lx = @scdd_lx,scdd_xz = @scdd_xz,ks_code = @ks_code,ks_name = @ks_name,seddbz = @seddbz,scdd_sl = @scdd_sl,delivery_date = @delivery_date,planned_start_time = @planned_start_time,planned_completion_time = @planned_completion_time,update_by = @create_by,update_time = GETDATE() WHERE scdd_code = @scdd_code;";
            try
            {
                using (SqlConnection connection = new SqlConnection(data))
                {
                    connection.Open();
                    var parameters = new
                    {
                        scdd_yxj = scdd.scdd_yxj,
                        scdd_code = scdd.scdd_code,
                        scdd_lx = scdd.scdd_lx,
                        scdd_xz = scdd.scdd_xz,
                        ks_code = scdd.ks_code,
                        ks_name = scdd.ks_name,
                        seddbz = scdd.seddbz,
                        scdd_sl = scdd.scdd_sl,
                        delivery_date = scdd.delivery_date,
                        planned_start_time = scdd.planned_start_time,
                        planned_completion_time = scdd.planned_completion_time,
                        create_by = scdd.create_by,
                    };

                    var affectedRows = connection.Execute(sql, parameters);
                    return affectedRows;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
        }
        public int UpdateMesSCDDJD(int jd, string code)
        {


            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$" update mes_scdd set scdd_jd=scdd_jd+{jd} where scdd_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
